IF OBJECT_ID('fGetSegmentPath') IS NOT NULL 
BEGIN 
    DROP FUNCTION fGetSegmentPath
    IF OBJECT_ID('fGetSegmentPath') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fGetSegmentPath >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fGetSegmentPath >>>'
END
GO
CREATE FUNCTION dbo.fGetSegmentPath
(
    @SegmentId int = NULL
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE
        @Chars varchar(8000)
    ,   @ParentSegmentId int

    SELECT @Chars = SegmentName FROM Segment WHERE SegmentId = @SegmentId
    
    SELECT @ParentSegmentId = ParentSegmentId FROM Segment WHERE SegmentId = @SegmentId

    WHILE @ParentSegmentId IS NOT NULL
    BEGIN
        SET @SegmentId = @ParentSegmentId
        SELECT @Chars = SegmentName + '\' + @Chars FROM Segment WHERE SegmentId = @SegmentId
        SELECT @ParentSegmentId = ParentSegmentId FROM Segment WHERE SegmentId = @SegmentId
    END
RETURN @Chars
END

GO
GRANT EXECUTE ON fGetSegmentPath TO public
GO
IF OBJECT_ID('fGetSegmentPath') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fGetSegmentPath >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fGetSegmentPath >>>' 
GO 